package util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.io.IOUtils;

import bot.DataStorage;
import bot.FileStorage;
import bot.Util;

public class TransferFromFileToDatabase {
	public static void main(String[] args){
		List<String> places = new LinkedList<String>();
		BufferedReader reader = null;
		try {
			reader = new BufferedReader(new FileReader(new File("c:\\Users\\Jamison\\Documents\\PopulationInfo\\IowaLinks.txt")));
			String line = null;
			while ((line = reader.readLine()) != null){
				places.add(line);
			}
		} 
		catch (FileNotFoundException e) {
			e.printStackTrace();
		} 
		catch (IOException e) {
			e.printStackTrace();
		}
		finally{
			IOUtils.closeQuietly(reader);
		}
		DataStorage transfer = new FileStorage(new File("c:\\Users\\Jamison\\Documents\\PopulationInfo\\IowaData.txt"));
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName("org.sqlite.JDBC");
			con = DriverManager.getConnection("jdbc:sqlite:C:/Users/Jamison/Documents/wikipedia.s3db");
			for (String place: places){
				stmt = con.createStatement();
				StringBuilder builder = new StringBuilder(250);
				final String SPLIT = "', '";
				//start
				builder.append("insert into census_2010 values('");
				builder.append(transfer.getName(place));
				builder.append(SPLIT);
				builder.append(transfer.getType(place));
				builder.append(SPLIT);
				builder.append(Util.removeCommas(transfer.getTotalPopulation(place)));
				builder.append(SPLIT);
				builder.append(transfer.getPercentUnder18(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercent18to24(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercent25to44(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercent45to64(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentOver65(place));
				builder.append(SPLIT);
				builder.append(transfer.getMedianAge(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentMale(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentFemale(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentWhite(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentBlack(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentNative(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentAsian(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentPacific(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentOther(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentTwoOrMore(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentHispanic(place));
				builder.append(SPLIT);
				builder.append(transfer.getHouseholds(place));
				builder.append(SPLIT);
				builder.append(transfer.getFamilies(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentWithChildren(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentHusbandAndWife(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentMaleNoWife(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentFemaleNoHusband(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentNonFamily(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentLivingAlone(place));
				builder.append(SPLIT);
				builder.append(transfer.getPercentLivingAloneOver65(place));
				builder.append(SPLIT);
				builder.append(transfer.getAverageHouseholdSize(place));
				builder.append(SPLIT);
				builder.append(transfer.getAverageFamilySize(place));
				builder.append(SPLIT);
				builder.append(Util.removeCommas(transfer.getNumberOfHouses(place)));
				builder.append(SPLIT);
				builder.append(transfer.getAreaLand(place));
				builder.append(SPLIT);
				builder.append(transfer.getAreaWater(place));
				builder.append(SPLIT);
				builder.append(transfer.getSumlev(place));
				builder.append(SPLIT);
				builder.append(transfer.getState(place));
				builder.append(SPLIT);
				builder.append(transfer.getPlace(place));
				builder.append(SPLIT);
				String titleSplit = place.split("\\?title=")[1];
				builder.append(titleSplit.substring(0, titleSplit.length() - 12));
				//ending
				builder.append("');");
				stmt.executeUpdate(builder.toString());
				DbUtils.closeQuietly(stmt);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		finally{
			DbUtils.closeQuietly(con);
			DbUtils.closeQuietly(stmt);
			DbUtils.closeQuietly(rs);
		}
	}
}
